﻿Imports System.Text

Public Class WhereQueryExpression

    Private _FieldsNames As New Dictionary(Of Integer, String)
    Private _FieldsTypes As New Dictionary(Of Integer, Type)
    Private _FRelationalOperators As New Dictionary(Of Integer, String)
    Private _Operators As New Dictionary(Of Integer, String)
    Private _Values As New Dictionary(Of Integer, Object)
    Private _literalsValues As New Dictionary(Of Integer, String)
    Private _CurrentIndex As Integer = -1
    Private _LiteralExpression As New Dictionary(Of Integer, String)
    Public Function FieldName(Of T)(ByVal Field As String) As WhereQueryExpression
        _CurrentIndex += 1
        _FieldsNames.Add(_CurrentIndex, Field)
        _FRelationalOperators(_CurrentIndex) = Nothing
        _Operators(_CurrentIndex) = Nothing
        _Values(_CurrentIndex) = Nothing
        _literalsValues(_CurrentIndex) = Nothing
        _FieldsTypes(_CurrentIndex) = GetType(T)
        Return Me
    End Function

    Public Function FieldName(ByVal Field As String, ByVal t As Type) As WhereQueryExpression
        _CurrentIndex += 1
        _FieldsNames.Add(_CurrentIndex, Field)
        _FRelationalOperators(_CurrentIndex) = Nothing
        _Operators(_CurrentIndex) = Nothing
        _Values(_CurrentIndex) = Nothing
        _literalsValues(_CurrentIndex) = Nothing
        _FieldsTypes(_CurrentIndex) = t
        Return Me
    End Function

    'Public Function FieldName(ByVal Field As String, ByVal dbType As System.Data.DbType) As WhereQueryExpression
    '   _CurrentIndex += 1
    '    _FieldsNames.Add(_CurrentIndex, Field)
    '    Return Me
    'End Function


    Public Function [And]() As WhereQueryExpression

        _FRelationalOperators(_CurrentIndex) = " AND "
        Return Me
    End Function


    Public Function [Or]() As WhereQueryExpression

        _FRelationalOperators(_CurrentIndex) = " OR "
        Return Me
    End Function


    Public Function [isMajor](Optional ByVal LiteralValue As String = Nothing) As WhereQueryExpression
        _literalsValues(_CurrentIndex) = LiteralValue
        _Operators(_CurrentIndex) = " > "
        Return Me
    End Function

    Public Function [isMinor](Optional ByVal LiteralValue As String = Nothing) As WhereQueryExpression
        _literalsValues(_CurrentIndex) = LiteralValue

        _Operators(_CurrentIndex) = " < "
        Return Me
    End Function

    Public Function [isMajorOrEquals](Optional ByVal LiteralValue As String = Nothing) As WhereQueryExpression
        _literalsValues(_CurrentIndex) = LiteralValue

        _Operators(_CurrentIndex) = " >= "
        Return Me
    End Function

    Public Function [isMinorOrEquals](Optional ByVal LiteralValue As String = Nothing) As WhereQueryExpression
        _literalsValues(_CurrentIndex) = LiteralValue

        _Operators(_CurrentIndex) = " <= "
        Return Me
    End Function

    Public Function [isDistinct](Optional ByVal LiteralValue As String = Nothing) As WhereQueryExpression
        _literalsValues(_CurrentIndex) = LiteralValue

        _Operators(_CurrentIndex) = " <> "
        Return Me
    End Function


    Public Function [isLike](Optional ByVal LiteralValue As String = Nothing) As WhereQueryExpression
        _literalsValues(_CurrentIndex) = LiteralValue

        _Operators(_CurrentIndex) = " Like "
        Return Me
    End Function


    Public Function StartWith(Optional ByVal LiteralValue As String = Nothing) As WhereQueryExpression
        _literalsValues(_CurrentIndex) = LiteralValue

        _Operators(_CurrentIndex) = " StartWith "
        Return Me
    End Function

    Public Function LiteralExpression(ByVal pLiteralExpression As String) As WhereQueryExpression
        _CurrentIndex += 1

        _FieldsNames.Add(_CurrentIndex, Nothing)
        _FRelationalOperators(_CurrentIndex) = Nothing
        _Values(_CurrentIndex) = Nothing
        _literalsValues(_CurrentIndex) = Nothing
        _FieldsTypes(_CurrentIndex) = Nothing         
        _LiteralExpression(_CurrentIndex) = pLiteralExpression

        _Operators(_CurrentIndex) = "LITERAL"
        Return Me
    End Function

    Private Const NOT_IN_CLAUSE As String = " Not in "
    Public Function [isNotIn](ByVal strSelectClause As String) As WhereQueryExpression

        _Operators(_CurrentIndex) = NOT_IN_CLAUSE
        Return Me
    End Function

    Public Function [isEqual]() As WhereQueryExpression

        _Operators(_CurrentIndex) = " = "
        Return Me
    End Function

    Public Function [isEqualToValue](ByVal val As Object) As WhereQueryExpression

        _Operators(_CurrentIndex) = " = "
        ToValue(val)
        Return Me
    End Function
    Public Function ToValue(ByVal val As Object) As WhereQueryExpression

        _Values(_CurrentIndex) = val
        Return Me
    End Function

    Public Class QueryExpressionResult
        Public WhereSql As String
        Public Parameters As List(Of IDataParameter)
    End Class

    Public Function getExpression() As QueryExpressionResult
        Dim qe As New QueryExpressionResult

        Dim sbStrSql As New StringBuilder
        sbStrSql.AppendFormat(" Where ")
        ''''TODO: validaciones
        For i As Integer = 0 To _CurrentIndex
            Dim operador As String = ""

            If String.IsNullOrEmpty(_Operators(i)) Then
                operador = " = "
            Else
                operador = _Operators(i)
            End If

             

            Dim operadorRelacional As String = ""
            If Not String.IsNullOrEmpty(_Operators(i)) AndAlso _FRelationalOperators.ContainsKey(i) Then
                operadorRelacional = _FRelationalOperators(i)
            End If

            

            If operador = NOT_IN_CLAUSE Then
                sbStrSql.AppendFormat("{0} {1} ({2}) {3} ", _FieldsNames(i), operador, _Values(i), operadorRelacional)
            ElseIf operador = "LITERAL" Then
                sbStrSql.AppendFormat(" {0} {1} ", _LiteralExpression(i), operadorRelacional)

            ElseIf operador.Trim().Equals("StartWith", StringComparison.InvariantCultureIgnoreCase) Then

                Dim pam = gp(i)
                If qe.Parameters Is Nothing Then
                    qe.Parameters = New List(Of IDataParameter)
                Else

                    Dim g As Integer = 1
                    Dim pName = pam.ParameterName
                    While qe.Parameters.Where(Function(x) x.ParameterName.Equals(pName, StringComparison.InvariantCultureIgnoreCase)).Any()
                        pName = String.Format("{0}{1}", pam.ParameterName, g)
                        g += 1
                    End While
                    pam.ParameterName = pName
                End If

                If ConnectionType() = daHelper.ConnectionTypeEnum.SQLServer Then

                    sbStrSql.AppendFormat("{0} {1} {2} + '%' {3} ", _FieldsNames(i), " Like ", pam.ParameterName, operadorRelacional)

                Else
                    Throw New NotImplementedException("no se ha implementado el operador like para este tipo de conexión " & ConnectionType.ToString())
                End If


                qe.Parameters.Add(pam)
            ElseIf operador.Trim().Equals("like", StringComparison.InvariantCultureIgnoreCase) Then

                Dim pam = gp(i)
                If qe.Parameters Is Nothing Then
                    qe.Parameters = New List(Of IDataParameter)
                Else

                    Dim g As Integer = 1
                    Dim pName = pam.ParameterName
                    While qe.Parameters.Where(Function(x) x.ParameterName.Equals(pName, StringComparison.InvariantCultureIgnoreCase)).Any()
                        pName = String.Format("{0}{1}", pam.ParameterName, g)
                        g += 1
                    End While
                    pam.ParameterName = pName
                End If

                If ConnectionType() = daHelper.ConnectionTypeEnum.SQLServer Then

                    sbStrSql.AppendFormat("{0} {1} '%' + {2} + '%' {3} ", _FieldsNames(i), operador, pam.ParameterName, operadorRelacional)

                Else
                    Throw New NotImplementedException("no se ha implementado el operador like para este tipo de conexión " & ConnectionType.ToString())
                End If


                qe.Parameters.Add(pam)

            Else

                If Not String.IsNullOrEmpty(_literalsValues(i)) Then
                    sbStrSql.AppendFormat("{0} {1} ({2}) {3} ", _FieldsNames(i), operador, _literalsValues(i), operadorRelacional)
                Else
                    Dim pam = gp(i)
                    If qe.Parameters Is Nothing Then
                        qe.Parameters = New List(Of IDataParameter)
                    Else

                        Dim g As Integer = 1
                        Dim pName = pam.ParameterName
                        While qe.Parameters.Where(Function(x) x.ParameterName.Equals(pName, StringComparison.InvariantCultureIgnoreCase)).Any()
                            pName = String.Format("{0}{1}", pam.ParameterName, g)
                            g += 1
                        End While
                        pam.ParameterName = pName
                    End If

                    sbStrSql.AppendFormat("{0} {1} {2} {3} ", _FieldsNames(i), operador, pam.ParameterName, operadorRelacional)


                    qe.Parameters.Add(pam)
                End If


            End If


        Next

        qe.WhereSql = sbStrSql.ToString().Trim()
        If qe.WhereSql.EndsWith(" AND") Then
            qe.WhereSql = qe.WhereSql.Remove(qe.WhereSql.Length - 3)
        ElseIf qe.WhereSql.EndsWith(" OR") Then
            qe.WhereSql = qe.WhereSql.Remove(qe.WhereSql.Length - 2)
        End If
            Return qe

    End Function


    Private _dh As daHelper
    Private Function gp(ByVal _index As Integer) As IDataParameter
        If _dh Is Nothing Then
            _dh = New daHelper(cnnString)
        End If
        Return _dh.getParm(_FieldsNames(_index), _Values(_index), _FieldsTypes(_index))
    End Function


    Public Function ConnectionType() As daHelper.ConnectionTypeEnum
        If _dh Is Nothing Then
            _dh = New daHelper(cnnString)
        End If
        Return _dh.ConnectionType()
    End Function



    Private Class daHelper
        Inherits DataAccessBase
        Public Sub New(ByVal cnnString As String)
            MyBase.New(cnnString)
        End Sub
        Public Function getParm(ByVal paramName As String, ByVal o As Object, ByVal t As Type) As IDataParameter
            Dim p = MyBase.getParam(getParameterPrefix() & paramName, o, MyBase.getDbType(t))

           

            Return p
        End Function
    End Class


    Public Overrides Function Equals(ByVal obj As Object) As Boolean
        Throw New ApplicationException("Not implemented")
    End Function



    'Private Function getColonedValue(ByVal inde As Integer, ByVal value As Object)
    '    If TypeOf _FieldsNames(inde) Is String Then
    '        Return String.Format("'{0}'", value)
    '    End If

    '    Return String.Format("{0}", value)

    'End Function

    Private cnnString As String
    Public Sub New(ByVal clsDef As EntityBase)
        cnnString = clsDef.ClassAttribute().DataBaseName
    End Sub

    Public Shared Function [NewQuery](ByVal clsDef As EntityBase) As WhereQueryExpression
        Return New WhereQueryExpression(clsDef)
    End Function

End Class
